package com.sangmo.fts.training.service;

import com.sangmo.boot.framework.data.filter.NativeQueryBuilder;
import com.sangmo.boot.framework.data.filter.PageResult;
import com.sangmo.boot.framework.data.filter.PageSearch;
import com.sangmo.boot.framework.plugin.dataview.crud.service.BaseViewService;
import com.sangmo.boot.framework.plugin.dataview.data.IDataViewRepo;
import com.sangmo.boot.framework.plugin.dataview.spi.jpa.NativeQueryBuilderFactory;
import com.sangmo.fts.clazz.model.Clazz;
import com.sangmo.fts.clazz.service.ClazzService;
import com.sangmo.fts.teacher.vo.ScoreDistributionRatioView;
import com.sangmo.fts.teacher.vo.ScoreDistributionView;
import com.sangmo.fts.training.interfaces.ScoreCreation;
import com.sangmo.fts.training.vo.UserRankView;
import com.sangmo.fts.training.model.Score;
import com.sangmo.fts.training.repo.ScoreRepo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional
public class ScoreEntityService extends BaseViewService<Score> {

    @Autowired
    private ScoreRepo scoreRepo;

    @Autowired
    private ClazzService clazzService;

    @Override
    protected IDataViewRepo<Score> getRepo() {
        return scoreRepo;
    }

    @Override
    protected void onCreate(Score entity, Object bo) {
        super.onCreate(entity, bo);
        ScoreCreation creation = (ScoreCreation) bo;
        Clazz clazz = clazzService.findByStudentId(creation.getUserId());
        entity.setClazzId(clazz.getId());
    }

    @Override
    public PageResult<Score> search(PageSearch ps) {

        return super.search(ps);
    }

    protected List<Score> findByTrainingId(Long trainingId) {
        return scoreRepo.findByTrainingId(trainingId);
    }

    protected List<Score> saveAll(List<Score> scores) {
        return scoreRepo.saveAll(scores);
    }

    protected List listRankingByExaminationId(Long examinationId) {
        return null;
    }

    protected Score findLatestByUserId(Long userId) {
        return scoreRepo.findFirstByUserIdAndStateOrderByIdDesc(userId, Score.States.COMPLETED);
    }

    protected Score findLatestByUserIdAndMode(Long userId, String mode) {
        return scoreRepo.findLatestByUserIdAndMode(userId, mode);
    }

    public UserRankView findRankingByExaminationIdAndUserId(Long userId, Long examinationId) {
//        Score score = scoreRepo.findFirstByUserIdAndStateOrderByIdDesc(userId, Score.States.COMPLETED);
        NativeQueryBuilder builder = NativeQueryBuilderFactory.create()
                .selectForm("SELECT a.id, a.user_id, a.final_mark, a.rownum AS ranking, a.examination_id FROM (SELECT s.id,s.examination_id,s.user_id,s.final_mark, CASE WHEN @rowtotal = s.final_mark THEN @rownum " +
                        " WHEN @rowtotal \\:= s.final_mark THEN @rownum \\:=@rownum + 1 " +
                        " WHEN @rowtotal = 0 THEN @rownum \\:=@rownum + 1 END AS rownum " +
                        "FROM (SELECT id, user_id, final_mark, examination_id FROM score WHERE examination_id = :examinationId ORDER BY final_mark DESC) AS s, " +
                        " (SELECT @rownum \\:= 0 ,@rowtotal \\:= NULL) AS r ) AS a")
                .where(" a.user_id = :userId ")
                .putVariable("examinationId", examinationId)
                .putVariable("userId", userId)
                .transform(UserRankView.class);

        return (UserRankView)builder.buildOne();
    }

    public ScoreDistributionView findDistributionByExaminationId(Long examinationId) {
        ScoreDistributionView result = (ScoreDistributionView) NativeQueryBuilderFactory.create()
                .putAlias("a", Score.class)
                .selectForm("SELECT\n" +
                        "count(id) as total," +
                        "count(case when a.final_mark>=0 and a.final_mark<=60 then 1 end) as less_than60," +
                        "count(case when a.final_mark>60 and a.final_mark<=70 then 1 end) as between60_and70," +
                        "count(case when a.final_mark>70 and a.final_mark<=80 then 1 end) as between70_and80," +
                        "count(case when a.final_mark>80 and a.final_mark<=90 then 1 end) as between80_and90," +
                        "count(case when a.final_mark>90 and a.final_mark<=100 then 1 end) as greater_than90 " +
                        " FROM score a")
                .where("a.examination_id = :examinationId ")
                .putVariable("examinationId", examinationId)
                .transform(ScoreDistributionView.class)
                .buildOne();

        return result;
    }

    public ScoreDistributionRatioView findDistributionRatioByExaminationId(Long examinationId) {
        ScoreDistributionRatioView result = (ScoreDistributionRatioView) NativeQueryBuilderFactory.create()
                .selectForm("select\n" +
                        "sum(case when 0<=final_mark and final_mark<= 60 then 1 else 0 end)/count(id)  less_than60, \n" +
                        "sum(case when 60<final_mark and final_mark<= 70 then 1 else 0 end)/count(id)  between60_and70, \n" +
                        "sum(case when 70<final_mark and final_mark<=80 then 1 else 0 end)/count(id)  between70_and80, \n" +
                        "sum(case when 80<final_mark and final_mark<=90 then 1 else 0 end)/count(id)  between80_and90,\n" +
                        "sum(case when 90<final_mark and final_mark<=100 then 1 else 0 end)/count(id)  greater_than90\n" +
                        "from score")
                .where(" examination_id = :examinationId ")
                .putVariable("examinationId", examinationId)
                .transform(ScoreDistributionRatioView.class)
                .buildOne();

        return result;
    }

    protected int countByExaminationId(Long examinationId) {
        return scoreRepo.countByExaminationId(examinationId);
    }



}
